﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccessObjectLayer
{
    internal class DBPartRoute : DBClass, IDBPartRoute
    {
        /// <summary>
        /// Builds a query with a 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        /// <returns>System.String.</returns>
        protected override string BuildQuery(string wClause)
        {
            var query = "SELECT Id, Distance, Time, FK_LocationEndId, FK_LocationStartId, Status FROM PartRoute ";
            if (wClause.Length > 0)
            {
                query += "WHERE " + wClause;
            }
            return query;
        }

        /// <summary>
        /// Gets all part routes from the database.
        /// </summary>
        /// <param name="routeId">The route identifier.</param>
        /// <returns>List{PartRoute}.</returns>
        public List<PartRoute> GetAllPartRoutes(long routeId)
        {
            return MiscWhere("FK_RouteId = " + routeId).Cast<PartRoute>().ToList();
        }

        /// <summary>
        /// Gets a part route from the database from an id.
        /// </summary>
        /// <param name="partRouteId">The part route identifier.</param>
        /// <returns>PartRoute.</returns>
        public PartRoute GetPartRoute(long partRouteId)
        {
            return (PartRoute)
                SingleWhere("Id = " + partRouteId);
        }

        /// <summary>
        /// Inserts a part route in the database and associates it to a route.
        /// </summary>
        /// <param name="partRoute">The part route.</param>
        /// <param name="routeId">The route identifier.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns>the number of affected rows</returns>
        /// <exception cref="System.Exception">The partroute was not inserted</exception>
        public int InsertPartRoute(PartRoute partRoute, long routeId, SqlTransaction transaction)
        {
            int nextId = GetMax.GetMaxId("SELECT MAX(Id) FROM PartRoute");
            nextId = nextId + 1;
            int rc = -1;

            const string query = "INSERT INTO PartRoute(Id, Distance, Time,Status,FK_LocationStartId,FK_LocationEndId,FK_RouteId)" +
                                 "VALUES(@Id, @Distance, @Time, @Status, @StationStartId, @StationEndId,@RouteId)";
            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                var cmdIns = SqlCommand(connection, out transaction);

                try
                {
                    cmdIns.CommandText = query;
                    using (cmdIns)
                    {
                        cmdIns.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = nextId });
                        cmdIns.Parameters.Add(new SqlParameter("@Distance", SqlDbType.Float)
                        {
                            Value = partRoute.Distance
                        });
                        cmdIns.Parameters.Add(new SqlParameter("@Time", SqlDbType.Float)
                        {
                            Value = partRoute.Time.TotalMinutes
                        });
                        cmdIns.Parameters.Add(new SqlParameter("@StationStartId", SqlDbType.BigInt)
                        {
                            Value = partRoute.LocationStart.Id
                        });
                        cmdIns.Parameters.Add(new SqlParameter("@StationEndId", SqlDbType.BigInt)
                        {
                            Value = partRoute.LocationEnd.Id
                        });
                        cmdIns.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int) { Value = partRoute.PartRouteStatus });
                        cmdIns.Parameters.Add(new SqlParameter("@RouteId", SqlDbType.BigInt) { Value = routeId });

                        rc = cmdIns.ExecuteNonQuery();
                    }
                } // end try
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Insert in DBPartRoute");
                }
                if (rc == -1)
                    throw new Exception("The partroute was not inserted");
                return rc;
            }
        }

        /// <summary>
        /// Updates a part route in the database associated to a route.
        /// </summary>
        /// <param name="partRoute">The part route.</param>
        /// <param name="routeId">The route identifier.</param>
        /// <returns>the number of affected rows</returns>
        /// <exception cref="System.Exception">The partroute was not updated</exception>
        public int UpdatePartRoute(PartRoute partRoute, long routeId)
        {
            int rc = -1;
            const string query = "UPDATE PartRoute SET " +
                                 "Distance = @Distance " +
                                 "Time = @Time " +
                                 "FK_LocationEndId = @FK_LocationEndId " +
                                 "FK_LocationStartId = @FK_LocationStartId " +
                                 "Status = @Status " +
                                 "FK_RouteId = @FK_RouteId " +
                                 " WHERE Id = @Id";
            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmdUpd = SqlCommand(connection, out transaction);
                try
                {
                    cmdUpd.CommandText = query;

                    using (cmdUpd)
                    {
                        cmdUpd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = partRoute.Id });
                        cmdUpd.Parameters.Add(new SqlParameter("@Time", SqlDbType.Float) { Value = partRoute.Time });
                        cmdUpd.Parameters.Add(new SqlParameter("@FK_StationEndId", SqlDbType.BigInt) { Value = partRoute.LocationStart });
                        cmdUpd.Parameters.Add(new SqlParameter("@FK_StationStartId", SqlDbType.BigInt) { Value = partRoute.LocationEnd });
                        cmdUpd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar) { Value = partRoute.PartRouteStatus });
                        cmdUpd.Parameters.Add(new SqlParameter("@FK_RouteId", SqlDbType.BigInt) { Value = routeId });
                        rc = cmdUpd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Update in DBPartRoute");
                }
            }
            if (rc == -1)
                throw new Exception("The partroute was not updated");
            return rc;
        }

        /// <summary>
        /// Removes a part route from the database.
        /// </summary>
        /// <param name="partRouteId">The part route identifier.</param>
        /// <returns>the number of affected rows</returns>
        /// <exception cref="System.Exception">The partroute was not removed</exception>
        public int RemovePartRoute(long partRouteId)
        {
            int rc = -1;
            const string query = "DELETE FROM PartRoute WHERE Id = @Id";
            var dbCon = new DBCon();
            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdDel = SqlCommand(connection, out transaction);
                try
                {
                    cmdDel.CommandText = query;

                    using (cmdDel)
                    {
                        cmdDel.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = partRouteId });
                        rc = cmdDel.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Delete in DBPartRoute");
                }
            }
            if (rc == -1)
                throw new Exception("The partroute was not removed");
            return (rc);
        }

        /// <summary>
        /// Inserts a part route in the database and associates it to a route.
        /// </summary>
        /// <param name="partRoutes">The part routes.</param>
        /// <param name="nextId">The next identifier.</param>
        /// <param name="con">The con.</param>
        /// <param name="transaction">The transaction.</param>
        public void InsertPartRoutes(IEnumerable<PartRoute> partRoutes, int nextId, SqlConnection con, SqlTransaction transaction)
        {
            int nextPartId = GetMax.GetMaxId("SELECT MAX(Id) FROM PartRoute");
            nextPartId = nextPartId + 1;
            var cmdText =
                new StringBuilder(
                    "INSERT INTO PartRoute(Id, Distance, Time,FK_LocationStartId,FK_LocationEndId,FK_RouteId,Status) VALUES ");
            var args = new List<Tuple<long, double, double, long, long, long, string>>();
            foreach (PartRoute partRoute in partRoutes)
            {
                int cnt = args.Count;
                if (cnt != 0)
                {
                    cmdText.Append(",");
                }
                cmdText.AppendFormat("(@id{0}, @distance{0}, @time{0}, @startId{0}, @endId{0}, @routeId{0}, @status{0})", cnt);
                args.Add(new Tuple<long, double, double, long, long, long, string>(nextPartId + cnt, partRoute.Distance,
                    partRoute.Time.TotalMinutes, partRoute.LocationStart.Id, partRoute.LocationEnd.Id, nextId, partRoute.PartRouteStatus.ToString()));
            }
            if (args.Count != 0)
            {
                using (SqlCommand cmd = new SqlCommand(cmdText.ToString(), con, transaction))
                {
                    for (var i = 0; i != args.Count; i++)
                    {
                        cmd.Parameters.AddWithValue("@id" + i, args[i].Item1);
                        cmd.Parameters.AddWithValue("@distance" + i, args[i].Item2);
                        cmd.Parameters.AddWithValue("@time" + i, args[i].Item3);
                        cmd.Parameters.AddWithValue("@startId" + i, args[i].Item4);
                        cmd.Parameters.AddWithValue("@endId" + i, args[i].Item5);
                        cmd.Parameters.AddWithValue("@routeId" + i, args[i].Item6);
                        cmd.Parameters.AddWithValue("@status" + i, args[i].Item7);
                    }
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Builds an object from either a dataset or a datareader The bool 'connected' defines if
        /// it is one or the other
        /// </summary>
        /// <param name="dataSetOrDataReader">A datarow or datareader.</param>
        /// <param name="connected">if set to <c>true</c> [connected](datareader).</param>
        /// <returns>Object to be cast to the correct type in the specific implementation</returns>
        protected override object BuildObject(object dataSetOrDataReader, bool connected)
        {
            PartRoute partRoute = null;
            if (connected)
            {
                var dataReader = dataSetOrDataReader as SqlDataReader;

                IDBStation dbStation = new DBStation();
                if (dataReader != null)
                    partRoute = new PartRoute
                    {
                        Id = (long)dataReader["Id"],
                        Distance = (double)dataReader["Distance"],
                        Time = TimeSpan.FromMinutes((double)dataReader["Time"]),
                        LocationStart = dbStation.GetStation((long)dataReader["FK_LocationStartId"]),
                        LocationEnd = dbStation.GetStation((long)dataReader["FK_LocationEndId"]),
                        PartRouteStatus =
                            (PartRouteStatusEnum)Enum.Parse(typeof(PartRouteStatusEnum), (string)dataReader["Status"])
                    };
            }
            else
            {
                var dataRow = dataSetOrDataReader as DataRow;

                IDBStation dbStation = new DBStation();
                if (dataRow != null)
                    partRoute = new PartRoute
                    {
                        Id = (long)dataRow["Id"],
                        Distance = (double)dataRow["Distance"],
                        Time = TimeSpan.FromMinutes((double)dataRow["Time"]),
                        LocationStart = dbStation.GetStation((long)dataRow["FK_LocationStartId"]),
                        LocationEnd = dbStation.GetStation((long)dataRow["FK_LocationEndId"]),
                        PartRouteStatus =
                            (PartRouteStatusEnum)Enum.Parse(typeof(PartRouteStatusEnum), (string)dataRow["Status"])
                    };
            }
            object o = partRoute;
            return o;
        }
    }
}